package com.augurit.gis.common.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.augurit.gis.common.dao.ConnectionFactory;





/**
 * <p>Title: 拼音首字母缩写生成类</p>
 * <p>Description: 生成拼音简写，并将拼音简写写进数据库。</p>
 *  <p>Description: 在main方法中配置好以后运行即可</p>
 * @author 范少辉
 * @date 2009-04-20
 */
public class SpellService {
	/**
	 *生成表的拼音简写，并将拼音简写写进对应的数据库表。
	 *@param tableName 需要生成拼音的表名
	 *@param columnName 需要生成的列名
	 *@param spellColumnName 拼音存放的列名
	 * @param key 需要进行生成拼音简写的关键字
	 * @param onlyBuildNull 是否只生成拼音简写字段为null的
	 * @throws SQLException
	 */
	public void build_spell(String tableName,String columnName,String spellColumnName,String key,boolean onlyBuildNull) throws SQLException{
		Connection connection = null;
		Statement readStatement = null;
		Statement writeStatement = null;
		long t1= System.currentTimeMillis();
		try {
			connection = ConnectionFactory.getMisConnection();
			readStatement = connection.createStatement();
			writeStatement = connection.createStatement();
			String sql= "select a.objectId,a."+columnName+" from "+tableName+" a where 1=1";
			if(!FormUtil.isEmpty(key)){
				sql += " and a."+columnName+" like '%"+SQLUtil.convertString4Sql(key)+"%'";
			}
			if(onlyBuildNull){
				sql += " and a."+spellColumnName+" is null";
			}
			System.out.println(sql);
			
			ResultSet resultSet = readStatement.executeQuery(sql);
			int successCount = 0;
			int failCount = 0;
			List<String> failList = new ArrayList<String>();
			
			while(resultSet.next()){
				String objectId = resultSet.getString("objectId");
				String name = resultSet.getString(columnName);
				String spellHead = SpellUtil.getSpellHead(name);
				sql = "update "+tableName+" t set t."+spellColumnName+"="+SQLUtil.string2SqlString(spellHead)+" where t.objectId="+objectId;
				int temp = writeStatement.executeUpdate(sql);
				if(temp>0){
					successCount++;
				}else{
					failList.add("objectId="+objectId+"# name="+name);
					failCount++;
				}
				if(successCount%1000==0&&successCount!=0){
					System.out.println("已经成功转换了"+successCount+"个"+tableName);
				}
			}
			System.out.println("成功要素有 "+successCount+" 个!");
			System.out.println("有 "+failCount+" 个转换失败!");
			StringUtil.printList(failList);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(connection!=null){
				connection.close();
			}
			if(readStatement!=null){
				readStatement.close();
			}
			if(writeStatement!=null){
				writeStatement.close();
			}
		}
		long t2= System.currentTimeMillis();
		StringUtil.showTimeMsg(t1, t2, "buildRiverSpell");
	}
	/**
	 * 合并多列地址成一列
	 * @param mzTableName 需要进行合并的表名
	 * @param fields	需要进行合并的字段
	 * @param addressTable 合并以后地址存放的表名
	 * @param addressField	存放合并地址的列
	 * @throws SQLException
	 */
	public void build_mzAddress(String mzTableName,String[] fields,String addressTable,String addressField) throws SQLException
	{
		Connection connection = null;
		Statement readStatement = null;
		Statement writeStatement = null;
		long t1= System.currentTimeMillis();
		try {
			connection = ConnectionFactory.getSdeConnection();
			readStatement = connection.createStatement();
			writeStatement = connection.createStatement();
			String sql="select objectId,";
			for(int i=0;i<fields.length;i++)
			{
				sql+="a."+fields[i]+",";
			}
			sql=sql.substring(0,sql.length()-1);
			sql+=" from "+mzTableName+" a where 1=1";
			System.out.println(sql);
			ResultSet resultSet = readStatement.executeQuery(sql);
			int successCount = 0;
			int failCount = 0;
			List<String> failList = new ArrayList<String>();
			
			while(resultSet.next()){
				String objectId = resultSet.getString("objectId");
				String address="";
				for(int i=0;i<fields.length;i++)
				{
					address+=resultSet.getString(fields[i]);
				}
				//sql = "update "+addressTable+" t set t."+addressField+"="+address+" where t.objectId="+objectId;
				sql="insert into "+addressTable+"(objectid,"+addressField+") values("+objectId+",'"+address+"')";
				//System.out.println(sql);
				//break;
				int temp = writeStatement.executeUpdate(sql);
				if(temp>0){
					successCount++;
				}else{
					failList.add("objectId="+objectId);
					failCount++;
				}
				if(successCount%1000==0&&successCount!=0){
					System.out.println("已经成功转换了"+successCount+"个"+addressTable);
				}
			}
			System.out.println("成功要素有 "+successCount+" 个!");
			System.out.println("有 "+failCount+" 个转换失败!");
			StringUtil.printList(failList);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(connection!=null){
				connection.close();
			}
			if(readStatement!=null){
				readStatement.close();
			}
			if(writeStatement!=null){
				writeStatement.close();
			}
		}
		long t2= System.currentTimeMillis();
		StringUtil.showTimeMsg(t1, t2, "buildAddress");
	}
	
	
	public static void main(String[] args) throws SQLException {
		SpellService service = new SpellService();
		String key = "";
		//service.build(tableName,columnName,spellColumnName,key,false);
		//service.build_spell("gis_tracnl_for_search","street","street_spell",key,false);
		//service.build_spell("gis_mz_tt_for_search","doornum","doornum_spell",key,false);
		//合并地址字段代码
		//String[] fields=new String[]{"poi_pro","poi_city","poi_town","streetname","doornum"};
		//service.build_mzAddress("sde_mz_tt",fields,"gis_mz_tt_for_search","doornum");
		
		//地址拼音生成
		//service.build_spell("gis_mz_tt_for_search","doornum","doornum_spell",key,false);
	}
}
